package com.sky.service.impl;

import com.alibaba.fastjson.JSON;
import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.OrderDetail;
import com.sky.entity.Orders;
import com.sky.mapper.OrderDetailMapper;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private OrderDetailMapper orderDetailMapper;
    @Autowired
    private WorkspaceService workspaceService;

    /**
     * 营业额统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
        //当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> localDateList = getLocalDateList(begin, end);

        //当前集合用于存放每天的营业额
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : localDateList){
            //查询date日期对应的营业额数据，营业额是指：状态为“已完成”的订单金融合计
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            map.put("status", Orders.COMPLETED);
            Double turnover = orderMapper.sumByMap(map);
            if(turnover == null) turnover = (double) 0;
            turnoverList.add(turnover);
        }

        //返回结果
        return TurnoverReportVO.builder()
                .dateList(StringUtils.join(localDateList, ","))
                .turnoverList(StringUtils.join(turnoverList, ","))
                .build();
    }

    /**
     * 用户统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
        //当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> localDateList = getLocalDateList(begin, end);

        //当前集合用于存放每天的用户新增
        List<Double> userAllList = new ArrayList<>();
        List<Double> userList = new ArrayList<>();
        for (LocalDate date : localDateList){
            //生成当天最早和最晚时间
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            Map map = new HashMap();
            map.put("end", endTime);

            //先查找总用户量
            Double userAll = userMapper.sumByMap(map);
            if(userAll == null) userAll = (double) 0;
            userAllList.add(userAll);

            //再查找新增用户
            map.put("begin", beginTime);
            Double user = userMapper.sumByMap(map);
            if(user == null) user = (double) 0;
            userList.add(user);
        }

        //返回结果
        return UserReportVO.builder()
                .dateList(StringUtils.join(localDateList, ","))
                .totalUserList(StringUtils.join(userAllList, ","))
                .newUserList(StringUtils.join(userList, ","))
                .build();
    }

    /**
     * 订单统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderReportVO getOrdersStatistics(LocalDate begin, LocalDate end) {
        //当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> localDateList = getLocalDateList(begin, end);

        //当前集合用于存放每天的用户新增
        List<Double> orderCountList = new ArrayList<>();
        List<Double> validOrderCountList = new ArrayList<>();
        int totalOrderCount = 0, validOrderCount = 0;
        for (LocalDate date : localDateList){
            //生成当天最早和最晚时间
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);

            //先查找每日总订单量
            Double orderAllSum = orderMapper.sumByMap(map);
            if(orderAllSum == null) orderAllSum = (double) 0;
            orderCountList.add(orderAllSum);
            totalOrderCount += orderAllSum;

            //再查找每日新增订单量
            map.put("status", Orders.COMPLETED);
            Double orderSum = orderMapper.sumByMap(map);
            if(orderSum == null) orderSum = (double) 0;
            validOrderCountList.add(orderSum);
            validOrderCount += orderSum;
        }

        //返回结果
        return OrderReportVO.builder()
                .dateList(StringUtils.join(localDateList, ","))
                .orderCountList(StringUtils.join(orderCountList, ","))
                .validOrderCountList(StringUtils.join(validOrderCountList, ","))
                .totalOrderCount(totalOrderCount)
                .validOrderCount(validOrderCount)
                .orderCompletionRate(1.0 * validOrderCount/totalOrderCount)
                .build();
    }

    //重点在拿到数据之后的处理
//    /**
//     * 查询销量排名top10接口(自己写的)
//     * @param begin
//     * @param end
//     * @return
//     */
//    @Override
//    public SalesTop10ReportVO getTop10Statistics(LocalDate begin, LocalDate end) {
//        //当前集合用于商品名称列表、销量列表
//        List<String> nameList = new ArrayList<>();
//        List<Integer> numberList = new ArrayList<>();
//
//        //存放所有结果
//        Map<String, Integer> map = new HashMap<>();
//
//        //生成最早和最晚时间
//        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
//        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);
//
//        //采集数据
//        List<Orders> ordersList = orderMapper.getByBeginAndEndTime(beginTime, endTime, Orders.COMPLETED);
//        for(Orders orders:ordersList){
//            List<OrderDetail> orderDetailList = orderDetailMapper.getByOrderId(orders.getId());
//            for(OrderDetail orderDetail:orderDetailList){
//                int num = map.getOrDefault(orderDetail.getName(), 0);
//                map.put(orderDetail.getName(), num + orderDetail.getNumber());
//            }
//        }
//
//        //排序，取前十
//        List<Map.Entry<String, Integer>> list = new ArrayList<>(map.entrySet());
//        Collections.sort(list, (o1, o2) -> {return o2.getValue() - o1.getValue();});
//        int res = 0;
//        for (Map.Entry<String, Integer> entry:list){
//            res ++;
//            nameList.add(entry.getKey());
//            numberList.add(entry.getValue());
//            if(res == 10) break;
//        }
//
//        //返回结果
//        return SalesTop10ReportVO.builder()
//                .nameList(StringUtils.join(nameList, ","))
//                .numberList(StringUtils.join(numberList, ","))
//                .build();
//    }

    /**
     * 查询销量排名top10接口(重点在sql语句)
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO getTop10Statistics(LocalDate begin, LocalDate end) {
        //生成最早和最晚时间
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        List<GoodsSalesDTO> goodsSalesDTOList = orderMapper.getSalesTop10(beginTime, endTime);

        List<String> nameList = goodsSalesDTOList.stream().map(GoodsSalesDTO::getName).collect(Collectors.toList());
        List<Integer> numberList = goodsSalesDTOList.stream().map(GoodsSalesDTO::getNumber).collect(Collectors.toList());

        //返回结果
        return SalesTop10ReportVO.builder()
                .nameList(StringUtils.join(nameList, ","))
                .numberList(StringUtils.join(numberList, ","))
                .build();
    }

    /**
     * 导出Excel报表接口
     * @param response
     */
    @Override
    public void exportBusinessData(HttpServletResponse response) {
        //1. 查询数据库，获取营业数据---查询最近30天的运营数据
        LocalDate beginTime = LocalDate.now().minusDays(30);
        LocalDate endTime = LocalDate.now().minusDays(1);

        //查询概览数据
        BusinessDataVO businessDataVO = workspaceService.getBusinessData(LocalDateTime.of(beginTime, LocalTime.MIN), LocalDateTime.of(endTime, LocalTime.MIN));

        //2. 获取POI将数据写入到Excel文件中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        try {
            //基于模板文件创建一个新的Excel文件
            XSSFWorkbook excel = new XSSFWorkbook(in);

            //获取表格文件的sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");

            //填充数据--时间
            sheet.getRow(1).getCell(1).setCellValue("时间：" + beginTime + "至" + endTime);

            //获得第4行
            XSSFRow row = sheet.getRow(3);
            row.getCell(2).setCellValue(businessDataVO.getTurnover());
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
            row.getCell(6).setCellValue(businessDataVO.getNewUsers());

            //获得第5行
            row = sheet.getRow(4);
            row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());
            row.getCell(4).setCellValue(businessDataVO.getUnitPrice());

            //填充明细数据
            for(int i=0; i<30; i++){
                LocalDate date = beginTime.plusDays(i);
                BusinessDataVO business = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));

                row = sheet.getRow(7+i);
                row.getCell(1).setCellValue(String.valueOf(date));
                row.getCell(2).setCellValue(business.getTurnover());
                row.getCell(3).setCellValue(business.getValidOrderCount());
                row.getCell(4).setCellValue(business.getOrderCompletionRate());
                row.getCell(5).setCellValue(business.getUnitPrice());
                row.getCell(6).setCellValue(business.getNewUsers());
            }

            //3. 通过输出流将Excel文件下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            //关闭资源
            out.close();
            excel.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }


    }

    /**
     * 根据开始和结束时间返回时间段
     * @param begin
     * @param end
     * @return
     */
    private List<LocalDate> getLocalDateList(LocalDate begin, LocalDate end){
        //当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> localDateList = new ArrayList<>();

        while (!begin.equals(end)){
            //日期计算
            localDateList.add(begin);
            begin = begin.plusDays(1);
        }
        localDateList.add(begin);
        return localDateList;
    }
}
